package com.zhenai.zaq.adm.provider.config;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ReflectUtil;
import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLName;
import com.alibaba.druid.sql.ast.SQLObject;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.expr.SQLPropertyExpr;
import com.alibaba.druid.sql.ast.statement.SQLExprTableSource;
import com.alibaba.druid.sql.visitor.SchemaStatVisitor;
import com.zhenai.sys.common.utils.StringUtils;
import com.zhenai.sys.framework.aspectj.context.VagueQueryUserInfoContext;
import com.zhenai.zaq.adm.common.annotation.VagueQueryUserInfo;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.lang.reflect.Method;
import java.util.*;

/**
 * 全局模糊查询   用户id，手机号，名称
 *
 * @author 汪焰
 * @date 2024/7/9 18:36
 */
@Intercepts(
        {
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
        }
)
@Slf4j
public class SqlVagueQueryUserInfoInterceptor implements Interceptor {

// 改写sql的时候会用到【坑：可能版本有问题，多试几个版本就行】
//<dependency>
//    <groupId>com.github.jsqlparser</groupId>
//    <artifactId>jsqlparser</artifactId>
//    <version>4.3</version>
//</dependency>

    static int MAPPED_STATEMENT_INDEX = 0;
    static int BOUND_SQL_INDEX = 5;

    static String USER_MAIN = "user_main";

    /**
     * 全局模糊查询   用户id，手机号，名称
     * <if test="keyType == 1"> and um.nickname like concat('%', #{keyword}, '%') </if>
     * <if test="keyType == 2"> and um.name like concat('%', #{keyword}, '%') </if>
     * <if test="keyType == 3"> and um.phone = #{keyword} </if>
     * <if test="keyType == 4"> and um.wxCity like concat('%', #{keyword}, '%') </if>
     * <if test="keyType == 5"> and um.id = #{keyword} </if>
     * <if test="keyType == 0"> and (
     * um.nickname like concat('%', #{keyword}, '%')
     * or um.name like concat('%', #{keyword}, '%')
     * or um.phone = #{keyword}
     * or um.wxCity like concat('%', #{keyword}, '%')
     * or um.id = #{keyword}
     * )</if>
     */
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        String id = ((MappedStatement) invocation.getArgs()[0]).getId();
        String className = id.substring(0, id.lastIndexOf("."));
        String methodName = id.substring(id.lastIndexOf(".") + 1);
        Method method = ReflectUtil.getMethodByNameIgnoreCase(Class.forName(className), methodName);
        VagueQueryUserInfo annotation = method.getAnnotation(VagueQueryUserInfo.class);
        if (ObjectUtil.isEmpty(annotation)) {
            log.info("无VagueQueryUserInfo注解method={}", id);
            return invocation.proceed();
        }
        VagueQueryUserInfoContext.VagueQueryData vagueQuery = VagueQueryUserInfoContext.getContext();
        if (Objects.isNull(vagueQuery) || StringUtils.isEmpty(vagueQuery.getKeyword())) {
            return invocation.proceed();
        }

        /**
         * 1、解析原始sql
         */
        // 获取拦截方法的参数
        Object[] args = invocation.getArgs();
        // 通过StatementHandler 获取执行SQL
        BoundSql boundSql = getBoundSql(args);
        String sql = boundSql.getSql();
        //k-表名 ， v-表的别名
        Map<String, String> tableNamesAndAliasMap = getTableNamesAndAlias(sql);
        String um = tableNamesAndAliasMap.get(USER_MAIN);
        if (StringUtils.isEmpty(um)) {
            return invocation.proceed();
        }

        /**
         * 2、获取sql片段
         */
        String sqlCondition = VagueQueryUserInfoContext.KeyTypeSql.getSql(vagueQuery.getKeyType(), vagueQuery.getKeyword(), um);

        /**
         * 3、改写sql
         */
        if (StringUtils.isNotEmpty(sql) && StringUtils.isNotEmpty(sqlCondition)) {
            String newSql = appendLastWhereCondition(sql, sqlCondition);
            MappedStatement mappedStatement = (MappedStatement) args[0];
            bindNewSql(invocation, mappedStatement, boundSql, newSql);
        }

        return invocation.proceed();
    }


    /**
     * 根据sql获取sql的表名及其别名，先只适配mysql
     *
     * @param sql
     * @return Map<表名, 别名>
     */
    public static Map<String, String> getTableNamesAndAlias(String sql) {
        List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, DbType.mysql.name());
        Map<String, String> tables = new HashMap<String, String>();

        for (SQLStatement stmt : stmtList) {
            SchemaStatVisitor visitor = SQLUtils.createSchemaStatVisitor(DbType.mysql);
            stmt.accept(visitor);
            for (SQLName originalTable : visitor.getOriginalTables()) {
                if (originalTable instanceof SQLIdentifierExpr) {
                    String tableName = ((SQLIdentifierExpr) originalTable).getName();
                    SQLObject parent = originalTable.getParent();
                    String alias = null;
                    if (parent instanceof SQLPropertyExpr) {
                        alias = ((SQLPropertyExpr) parent).getResolvedTableSource().getAlias();
                    } else if (parent instanceof SQLExprTableSource) {
                        alias = ((SQLExprTableSource) parent).getAlias();
                    }
                    if (null == alias) {
                        alias = tableName;
                    }
                    // 保存找到的第一个
                    tables.putIfAbsent(tableName, alias); //.put(alias, tableName);
                }
            }
        }
        return tables;
    }


    /**
     * 在最外层where追加条件
     *
     * @param sql:完整的原SQL
     * @param conditionFragment:条件片段
     * @return void
     * @date 2023/9/15 11:00
     */
    public static String appendLastWhereCondition(String sql, String conditionFragment) throws JSQLParserException {
		//  【坑：下面两行容易报错，pom坐标 多切换几个版本就行】
//<dependency>
//    <groupId>com.github.jsqlparser</groupId>
//    <artifactId>jsqlparser</artifactId>
//    <version>4.3</version>
//    <version>4.6</version>
//</dependency>
		
		  // 构建条件Expression，形如 a = 1 and b = 2，parseExpression可返回任何合法的SQL，不限于条件表达式
        Expression expression = CCJSqlParserUtil.parseExpression(conditionFragment);
//        BinaryExpression binaryExpression = new GreaterThanEquals();
//        binaryExpression.setLeftExpression(new Column("B"));
//        binaryExpression.setRightExpression(new LongValue(10));
		
        Select select = (Select) CCJSqlParserUtil.parse(sql);
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();

        // 获取where条件
        Expression where = plainSelect.getWhere();

      

        if (where == null) {
            plainSelect.setWhere(expression);
        } else {
            AndExpression andExpression = new AndExpression(where, expression);
            plainSelect.setWhere(andExpression);
        }

        System.out.println("改写后：" + plainSelect.toString());
        return plainSelect.toString();
    }

    private BoundSql getBoundSql(Object[] args) {
        // 通过StatementHandler 获取执行SQL
        MappedStatement mappedStatement = (MappedStatement) args[0];
        return mappedStatement.getBoundSql(args[1]);
    }

    private void bindNewSql(Invocation invocation, MappedStatement mappedStatement, BoundSql boundSql, String newSql) {
        /**
         * 1、构建新的 BoundSql
         */
        BoundSql newBoundSql = new BoundSql(mappedStatement.getConfiguration(), newSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
        /**
         * 2、构建新的 MappedStatement 把新的sql放到statement里
         */
        MappedStatement newMs = copyFromMappedStatement(mappedStatement, new BoundSqlSqlSource(newBoundSql));
        for (ParameterMapping mapping : boundSql.getParameterMappings()) {
            String prop = mapping.getProperty();
            if (boundSql.hasAdditionalParameter(prop)) {
                newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));
            }
        }

        /**
         * 3、修改上下文sql
         * 注意：上下文sql 有两处
         * 第一处是 MappedStatement 里面有个sql
         * 第二处是 invocation.getArgs() 第五个参数
         */
        final Object[] queryArgs = invocation.getArgs();
        queryArgs[MAPPED_STATEMENT_INDEX] = newMs;
        if (queryArgs.length >= 6) {
            queryArgs[BOUND_SQL_INDEX] = newBoundSql;
        }
    }

    private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
        MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());
        builder.resource(ms.getResource());
        builder.fetchSize(ms.getFetchSize());
        builder.statementType(ms.getStatementType());
        builder.keyGenerator(ms.getKeyGenerator());
        if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) {
            builder.keyProperty(ms.getKeyProperties()[0]);
        }
        builder.timeout(ms.getTimeout());
        builder.parameterMap(ms.getParameterMap());
        builder.resultMaps(ms.getResultMaps());
        builder.resultSetType(ms.getResultSetType());
        builder.cache(ms.getCache());
        builder.flushCacheRequired(ms.isFlushCacheRequired());
        builder.useCache(ms.isUseCache());
        return builder.build();
    }

    public static class BoundSqlSqlSource implements SqlSource {

        private final BoundSql boundSql;

        public BoundSqlSqlSource(BoundSql boundSql) {
            this.boundSql = boundSql;
        }

        @Override
        public BoundSql getBoundSql(Object parameterObject) {
            return boundSql;
        }
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
        Interceptor.super.setProperties(properties);
    }
}
